package database.live;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import org.apache.log4j.Logger;

import com.trackvoodoo.models.DbPopulateDocument.DbPopulate;
import com.trackvoodoo.models.DbPopulateDocument.DbPopulate.DbElement;
import com.trackvoodoo.models.DbType;

import database.connector.DBConnector;
import database.connector.DatabaseFactory;
import database.connector.MySqlConnector;


/*
 * The main mySQl connection class, this allowes to you run
 * 
 * select
 * insert
 * update
 * delete
 * getCount
 * 
 * or just get a prepared statement for multi use.. which you then execute ps.update() etc..
 * 
 */

public class MySqlDAO implements DatabaseDAO {

	
	private Logger log = Logger.getLogger(MySqlConnector.class);
	private DBConnector connector = null;
	
	public MySqlDAO(){
		
		//set connector up.
		connector = DatabaseFactory.getConnector();
	}
	
	public PreparedStatement getPreparedStatement(String query)
	{
		PreparedStatement ps = null;
		try{
		ps = connector.getConnection().prepareStatement(query);
		}catch(SQLException e)
		{
			log.error("Cannot create a prepared statement..");
		}
		return ps;
	}

	
	public static void addDBElement(DbPopulate db, int pos, String value)
	{
		DbElement a = db.addNewDbElement();
		a.setLocation(pos);
		a.setValue(value);
		a.setType(DbType.STRING);
	}
	
	public static void addDBElement(DbPopulate db, int pos, int value)
	{
		DbElement a = db.addNewDbElement();
		a.setLocation(pos);
		a.setValue(new Integer(value).toString());
		a.setType(DbType.INT);
	}
	
	public static void addDBElement(DbPopulate db, int pos, Date value)
	{
		DbElement a = db.addNewDbElement();
		a.setLocation(pos);
		a.setValue(value.toString());
		a.setType(DbType.DATE);
	}
	
	public static void addDBElement(DbPopulate db, int pos, Boolean value)
	{
		DbElement a = db.addNewDbElement();
		a.setLocation(pos);
		if (value)
		{
			a.setValue("True");
		}else
		{
		a.setValue("False");
		}
		a.setType(DbType.BOOLEAN);
	}
	
	public static int parseAndUpdateInsertDelete(String statement, DbPopulate dbPopulate)
	{
		int count = 0;
		if ( dbPopulate.getDbElementArray().length > 0 )
		{
			DbElement[] elementArray = dbPopulate.getDbElementArray();
			MySqlDAO d = (MySqlDAO) DatabaseFactory.getDAO();
			if (d.isOpen())
			{
				try{
					PreparedStatement p = d.getPreparedStatement(statement);
					
					for (DbElement e : elementArray)
					{
						if (e.getType() == DbType.INT)
						{
							p.setInt(e.getLocation(), new Integer(e.getValue()));
						}
						else if (e.getType() == DbType.STRING)
						{
							p.setString(e.getLocation(), e.getValue());
						}
						else if (e.getType() == DbType.DATE)
						{
							SimpleDateFormat formatter = new SimpleDateFormat("YYY-MM-DD");
							try {
								Date date = (Date) formatter.parse(e.getValue());
								p.setDate(e.getLocation(), date);
							} catch (ParseException e1) {
								e1.printStackTrace();
							}	
						}
						else if (e.getType() == DbType.BOOLEAN)
						{
							if (e.getValue().equalsIgnoreCase("True"))
							{
								p.setBoolean(e.getLocation(), Boolean.TRUE);
							}else
							{
								p.setBoolean(e.getLocation(), Boolean.FALSE);
							}
						}
					}
					
					count = p.executeUpdate();
				}
				catch (SQLException e)
				{
					System.out.println(e.getMessage());
				}
				finally
				{
					d.closeConnection();
				}
			}	
		}
		
		return count;
	}
	
	
	
	public ResultSet selectQuery(String query) {
		
		Statement createStatement= null;
		try {
			createStatement = connector.getConnection().createStatement();
			ResultSet results = createStatement.executeQuery(query);			
			return results;
		} catch (SQLException e) {
			
			log.error("Cannot created/excute statement..");
		}
		return null;
	}
	
	public boolean isOpen()
	{
		try {
			if (connector.getConnection().isClosed()) return false; // yuk
			return true;
		} catch (SQLException e) {
			log.error("Cannot get connection status..");
		}
		return false;
	}

	public int insert(String query) 
	{	
		return executeUpdate(query);
	}

	public int update(String query) 
	{
		return executeUpdate(query);
	}

	public int delete(String query)
	{
		return executeUpdate(query);
	}
	
	public int getCount(String query)
	{
		ResultSet r = selectQuery(query);
		try {
			r.next();
			return r.getInt(1);
		} catch (SQLException e) {
			
			log.error("Cannot get row count..");
			return -1;
		}
		
		
		
		
	}
	
	private int executeUpdate(String query)
	{
		Statement statement= null;
		try {
			statement = connector.getConnection().createStatement();
			int executeUpdate = statement.executeUpdate(query);
			return executeUpdate;
		}catch (SQLException e) {
			
			log.error("Cannot created/excute statement..");
		}
		return 0;
	}
	
	public boolean closeConnection()
	{
		if (connector != null)
		{
			connector.closeConnetion();
			log.info("Closed Connection..");
			return true;
		}else
		{
			log.info("No connection to close..");
			return false;
		}
	}
	

}
